﻿using System;
using System.Data.SqlTypes;
using System.Collections;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.SqlClient;
using System.Xml;

namespace ADOCsharpe
{
    class sqlConnection
    {
        private static string constr = "server=.;database=northwnd;intergrated security=sspi;";
        static void Main(string[] args)
        {
            ExecuteXmlReader();
            ExecuteScalar();
            ExecuteReader();
            ExcuteNonQuery();

        }

       public static void ExcuteNonQuery()
        {
            string select = "update customers set contactname='bob'where contactname='Maria Anders'";
            SqlConnection con = new SqlConnection(constr);
            con.Open();
            SqlCommand cmd = new SqlCommand(select, con);
            int rows = cmd.ExecuteNonQuery();
            Console.WriteLine("{0} rows returned", rows);
            con.Close();
        }

        public static void ExecuteReader()
        {
            string select = "select contactname,copmpanyname form customers";
            SqlConnection con = new SqlConnection(constr);
            con.Open();
            SqlCommand cmd = new SqlCommand(select, con);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine("Contact:{0,-24} Company: {1}",reader[0],reader[1]);
            }
        }

          static void ExecuteScalar()
        {
            String select = "SELECT COUNT(*)FROM Customers";
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(select, conn);
            object o = cmd.ExecuteScalar();
            Console.WriteLine(o);
        }

        static void ExecuteXmlReader()
        {
            string select = "SELECT ContactName,CompanyName " + "FRON Customers FOR XML AUTO";
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(select, conn);
            XmlReader xr = cmd.ExecuteXmlReader();
            xr.Read();
            string data;
            do
            {
                data = xr.ReadOuterXml();
                if (!string.IsNullOrEmpty(data))
                    Console.WriteLine(data);
            } while (!string.IsNullOrEmpty(data));
            conn.Close();
        }
    }
}
